-- liquibase formatted sql

-- changeset n.parkhomenko:V1695415703_create_permission_tables
CREATE TABLE access_control_objects
(
    id         INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    code       INT       NOT NULL UNIQUE,
    name       VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP          DEFAULT NULL
);

CREATE TABLE access_control_actions
(
    id         INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    code       INT       NOT NULL UNIQUE,
    name       VARCHAR(100),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP          DEFAULT NULL
);

CREATE TABLE permissions
(
    id         INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    object_id  INT       NOT NULL,
    action_id  INT       NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP          DEFAULT NULL,

    CONSTRAINT fk_permissions_objects FOREIGN KEY (object_id) REFERENCES access_control_objects (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_permissions_actions FOREIGN KEY (action_id) REFERENCES access_control_actions (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT permission_object_action_unique_idx UNIQUE (object_id, action_id)
);

CREATE TABLE roles
(
    id                     INT          NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    scope                  SMALLINT     NOT NULL,
    scope_target_entity_id INT                   DEFAULT NULL,
    name                   VARCHAR(100) NOT NULL,
    created_at             TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at             TIMESTAMP             DEFAULT NULL
);

CREATE TABLE role_permissions
(
    id            INT       NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    role_id       INT       NOT NULL,
    permission_id INT       NOT NULL,
    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP          DEFAULT NULL,

    CONSTRAINT fk_role_permission_role FOREIGN KEY (role_id) REFERENCES roles (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_role_permission_permission FOREIGN KEY (permission_id) REFERENCES permissions (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT role_permission_role_permission_unique_idx UNIQUE (role_id, permission_id)
);

CREATE TABLE user_entry_roles (
    id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    entry_type INT NOT NULL,
    entry_id INT NOT NULL,
    role_id INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NULL,

    CONSTRAINT user_entry_roles_type_id_role_unique UNIQUE (entry_type, entry_id, role_id)
);